Imports MyClasses
Imports System.Data
Imports System.Data.SqlClient


Public Class DUser

    'Public Shared Function GetUsers() As DataSet
    '    Dim strSql As String

    '    strSql = "SELECT MU.UserSeqID,MU.UserID,MU.PersonID,Password,Title,Fname,Mname,Lname," & _
    '             "Age FROM mpUser MU, mperson WHERE MU.Status='A' AND MU.PersonID=mperson.PersonID"
    '    Return DbHelper.ExecuteDataSet(strSql)
    'End Function
    '<< 
    'Public Shared Function GetUser() As DataTable
    '    Dim strSql As String
    '    Try
    '        strSql = "SELECT title,dispname FROM mPuser MU,mperson p WHERE MU.personid=p.personid"
    '        Return DbHelper.ExecuteDataTable(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "GetUser")
    '        Return Nothing
    '    End Try
    'End Function
    Public Shared Function GetUserID() As DataTable
        Dim strSql As String
        Try
            strSql = "SELECT distinct usr.UserId FROM mEmp E, mPuser USR where E.empid=USR.empid"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "GetUserID")
            Return Nothing
        End Try
    End Function
    '>> End 
    'Public Shared Function Save(ByVal User As Cuser, ByVal SaveUpdt As String) As Boolean
    '    Dim strSql As String
    '    Dim intResult As Integer
    '    Dim dbConnection As New dbAccess
    '    Try
    '        dbConnection.BeginTransaction()
    '        If SaveUpdt = "S" Then
    '            Dim param(8) As SqlParameter
    '            strSql = "INSERT INTO mPUser(UserSeqID,UserID,PersonID,EmpID,RoleID,Password,Status,LU,LD)" & _
    '                         "VALUES(@UserSeqID,@User_ID,@Person_ID,@EmpID,@roleid,@Password,@Status,@LU,@LD)"

    '            User.UsrSeqNo = DGeneral.GetSeqNum("USER")
    '            param(0) = New SqlParameter("UserSeqID", User.UsrSeqNo)
    '            param(1) = New SqlParameter("@User_ID", User.LoginID)
    '            param(2) = New SqlParameter("@Person_ID", User.PersonID)
    '            param(3) = New SqlParameter("@EmpID", User.EmployeeID)
    '            param(4) = New SqlParameter("@roleid", User.RoleId)
    '            param(5) = New SqlParameter("@Password", User.Password)
    '            param(6) = New SqlParameter("@Status", "A")
    '            param(7) = New SqlParameter("@LU", CCommon.GetUserID())
    '            param(8) = New SqlParameter("@LD", Now.Date())
    '            intResult = dbConnection.ExecuteNonQuery(strSql, param)
    '        Else
    '            Dim param(1) As SqlParameter
    '            strSql = "Update mPUser set RoleID = @roleid where EmpId = @EmpId"
    '            param(0) = New SqlParameter("@roleid", User.RoleId)
    '            param(1) = New SqlParameter("@EmpID", User.EmployeeID)
    '            intResult = dbConnection.ExecuteNonQuery(strSql, param)
    '        End If
    '        If intResult <> 0 Then
    '            dbConnection.CommitTransaction()
    '            Return True
    '        Else : Return False
    '        End If
    '    Catch ex As Exception
    '        dbConnection.RollBackTransaction()
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "Save")
    '        Return False
    '    End Try

    'End Function

    Public Shared Function Update(ByVal User As Cuser) As Boolean
        Dim strSql As String
        Dim param(8) As SqlParameter
        Try
            strSql = "UPDATE MU SET User_ID= @User_ID,JoiningDt=@doj,Password=@Password where UsrSeq_ID=@UsrSeq_ID"

            param(0) = New SqlParameter("@User_ID", User.LoginID)

            param(2) = New SqlParameter("@Password", User.Password)
            param(3) = New SqlParameter("@UsrSeq_ID", User.UsrSeqNo)

            Return DbHelper.ExecuteNonQuery(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "Update")
        End Try
    End Function

    Public Shared Function Delete(ByVal SeqID As Integer) As Boolean
        Dim strSql As String
        Try
            strSql = "UPDATE MU SET Status='X' where UsrSeq_ID=" & SeqID
            Return DbHelper.ExecuteNonQuery(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "Delete")
        End Try
    End Function

    Public Shared Function CheckUserPassword(ByVal UserID As String, ByVal Password As String) As Integer
        Dim strsql As String
        Dim dtDUsers As DataTable
        Try
            'select  UserName,UserCode from [user] 
            strsql = "SELECT * FROM [user] WHERE UserCode='" & UserID & "'" ' AND Password='" & Password & "'"
            dtDUsers = DbHelper.ExecuteDataTable(strsql)
            If dtDUsers.Rows.Count > 0 Then
                'If StrComp(dtDUsers.Rows(0).Item(5), Password) = 1 Then Password = ""
                dtDUsers = Nothing
                strsql = "SELECT * FROM [user] WHERE UserCode='" & UserID & "' AND Password='" & Password.Trim() & "'"
                dtDUsers = DbHelper.ExecuteDataTable(strsql)
                If dtDUsers.Rows.Count > 0 Then : Return 3
                Else : Return 2
                End If
            Else : Return 1
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "CheckUserPassword")
        End Try
    End Function

    'Code Start          ....09/03/2007
    Public Shared Function ChangePassword(ByVal UserID As String, ByVal Password As String) As Boolean
        Try
            Dim strSql As String
            strSql = "UPDATE mpUser SET Password='" & Password & "' WHERE UserID='" & UserID & "'"
            Return DbHelper.ExecuteNonQuery(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "ChangePassword")
        End Try
    End Function

    Public Shared Function GetUser(ByVal strUserID As String) As DataSet
        Try
            Dim strEmp As String
            strEmp = "SELECT UserID,Password,PersonID FROM mpuser WHERE UserID='" & strUserID & "'"
            Return DbHelper.ExecuteDataSet(strEmp)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "GetUser")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetUserList() As DataTable
        Try
            Dim strEmp As String
            strEmp = "SELECT  memp.EmpID,SeqEmpID as ID,mMiscCD.CodeDesc+' '+Fname+' '+Mname+' '+Lname AS Employee,mEmp.PersonId " & _
                    " FROM mEmp, mperson,mmisccd WHERE memp.EmpID NOT IN (SELECT memp.EmpID FROM mEmp,mpuser,mperson WHERE mEmp.PersonId = mpuser.PersonId " & _
                    " AND mEmp.PersonId=mperson.PersonId)AND mEmp.PersonId =mPerson.PersonId" & _
                    " AND codetype='TITLE' AND codeID=mPerson.title"
            Return DbHelper.ExecuteDataTable(strEmp)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "GetUserList")
            Return Nothing
        End Try

    End Function
    Public Shared Function GetUsers(ByVal intEmpID As Integer) As DataSet
        Try
            Dim strEmp As String

            strEmp = "SELECT SeqEmpID, mMiscCD.CodeDesc+' '+Fname+' '+Mname+' '+Lname AS Employee,mEmp.PersonId " & _
                    " FROM mEmp, mperson,mmisccd WHERE memp.EmpID NOT IN (SELECT memp.EmpID FROM mEmp,mpuser,mperson WHERE mEmp.PersonId = mpuser.PersonId " & _
                    " AND mEmp.PersonId=mperson.PersonId)AND mEmp.PersonId =mPerson.PersonId" & _
                    " AND codetype='TITLE' AND codeID=mPerson.title and EmpID = " & intEmpID
            Return DbHelper.ExecuteDataSet(strEmp)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DUser", "GetUsers")
            Return Nothing
        End Try

    End Function

    'Code End
End Class
